package com.jdbctest.numbertwo;

import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

public class StatementTest {
    //使用Statement的弊端:需要拼写SQL语句，并且存在SQL注入的问题
    //如果避免出现SQL注入: 只要用PreparedStatement(从Statement扩展而来)取代Statement

    @Test
    public void testLogin() {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入用户名:");

        String user = scanner.nextLine();

        System.out.println("请输入密码:");
        String password = scanner.nextLine();

        //SELECT user,password FROM user_table WHERE user = '1' or ' AND password = '=1 or '1' = '1'
        String sql = "SELECT user,password FROM user_table WHERE user = '" + user +"' AND password = '" + password;

        User returnUser = get(sql, User.class);
        if(returnUser != null) {
            System.out.println("登录成功");
        } else {
            System.out.println("用户名不存在或密码错误");
        }

    }

    //使用Statement实现对数据库的查询操作
    public <T> T get(String sql, Class<T> clazz) {
        T t = null;

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            //1.加载配置文件
            InputStream inputStream = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            //2.读取配置信息
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");

            //3.加载驱动
            Class.forName(driverClass);

            //4.获取连接
            connection = DriverManager.getConnection(url, user, password);

            //通过connection获取statement
            statement = connection.createStatement();

            //获取ResultSet
            resultSet = statement.executeQuery(sql);

            //获取结果集的元数据
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

            //获取结果集的列数
            int columnCount = resultSetMetaData.getColumnCount();

            //判断是否还有数据存在
            if(resultSet.next()) {
                t = clazz.newInstance();

                //for循环开始遍历数据
                for(int i = 0; i < columnCount; i++) {
                    //1.获取列的名称
                    String columName = resultSetMetaData.getColumnLabel(i + 1);

                    //2.根据列名获取对应1数据表中的数据
                    Object object = resultSet.getObject(columName);

                    //3.将数据表中得到的数据，封装进对象
                    Field field = clazz.getDeclaredField(columName);
                    field.setAccessible(true);
                    field.set(t, object);
                }
                return t;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            if(resultSet != null) {
                try {
                    resultSet.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if(statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if(connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
}
